const { Service } = require("egg");

class StudentService extends Service {
  async findAll () {
    // const sql = 'select * from tab_student'
    // let students = await this.app.mysql.query(sql)
    let students = await this.app.mysql.select('tab_student');
    return students
  }

  async saveOrUpdate (student) {
    if (student.id) {
      await this.app.mysql.update('tab_student', student)
    } else {
      await this.app.mysql.insert('tab_student', student)
    }
  }

  async deleteById (id) {
    await this.app.mysql.delete('tab_student', { id })
  }

  async pageQuery ({ page, pageSize, name, gender }) {
    page = parseInt(page)
    pageSize = parseInt(pageSize)
    // 校验name，gender有无
    let queryParams = { name, gender }
    for (let k in queryParams) {
      if (!queryParams[k]) {
        delete queryParams[k]
      }
    }
    // 多条件检索学生
    let students = await this.app.mysql.select('tab_student', {
      where: queryParams,
      limit: pageSize,
      offset: (page - 1) * pageSize
    })

    // 拼接sql语句完成多条件下total值的返回
    let sql = `select count(*) as total from tab_student`
    let keys = Object.keys(queryParams)
    if (keys.length > 0) {
      if (keys.length == 1) {
        sql += ` where ${keys[0]} = '${queryParams[keys[0]]}'`
      } else {
        for (let i = 1; i < keys.length; i++) {
          sql += ` where ${keys[0]} = '${queryParams[keys[0]]}'`
            + ` and ${keys[0 + i]} = '${queryParams[keys[0 + i]]}'`
        }
      }
    }
    sql += ` limit ${(page - 1) * pageSize},${pageSize}`
    let total = await this.app.mysql.query(sql)

    return {
      page,
      pageSize,
      total: total[0].total,
      list: students,
    }
  }
}

module.exports = StudentService